package SQLUtils;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.format.DateUtils;
import android.util.Log;

import com.example.zelonghu.test_all_without_network.AppContent;

import java.util.ArrayList;
import java.util.List;
import DateUtils.dateutils;
import SensorData.SensorData;
/**
 * @author zelonghu QQ:1032006226
 * @name test_all_without_network
 * @class name：SQLUtils
 * @class describe
 * @time 2019/4/13 8:42 PM
 * @change
 * @chang time
 * @class describe
 */
public class sqllite {
    /**
     * @author zelonghu
     * @time 2019/4/13  9:13 PM
     * @describe to create the sensingdatalist
     * @ret the name of the table
     */
    public static String createTableSDL(SQLiteDatabase db){
        try {
            //创建表SQL语句

            String stu_table = "create table sensingdatalist(id integer primary key autoincrement," +
                    "time varchar[10]," +
                    "type integer,value1 real," +
                    "value2 real,value3 real,flag integer)";
            //执行SQL语句
            db.execSQL(stu_table);
        }catch (Exception e){
            e.printStackTrace();
        }
        return "sensingdatalist";
    }

    /**
     * @author zelonghu
     * @time 2019/4/13  9:15 PM
     * @describe  insert the SensorData into the database
     */
    public static void insertSD(SQLiteDatabase db,String tablename,SensorData sd){
        //实例化常量值
        ContentValues cValue = new ContentValues();
        //添加数据
        cValue.put("time", sd.getTime());
        cValue.put("type",sd.getType());
        cValue.put("value1",sd.getValue1());
        cValue.put("value2",sd.getValue2());
        cValue.put("value3",sd.getValue3());
        cValue.put("flag",sd.getFlag());
        try {
            //调用insert()方法插入数据
            db.insert(tablename, null, cValue);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * @author zelonghu
     * @time 2019/4/13  9:18 PM
     * @describe 返回所有的数据库数据
     *          get all the SensorData into a List from the database
     */
    public static List<SensorData> querySD(SQLiteDatabase db, String tablename) {
        List<SensorData> sdl = new ArrayList<SensorData>();
        //查询获得游标
        Cursor cursor = db.query (tablename,null,null,
                null,null,null,null);
        //判断游标是否为空
        if(cursor.moveToFirst()) {
            //遍历游标
            Log.i("cursor count",cursor.getCount() +"");
            while(!cursor.isAfterLast()){
                SensorData sd = new SensorData();
                int id = cursor.getInt(0);
                sd.setTime(cursor.getString(1));
                sd.setType(cursor.getInt(2));
                sd.setValue1(cursor.getDouble(3));
                sd.setValue2(cursor.getDouble(4));
                sd.setValue3(cursor.getDouble(5));
                sd.setFlag(cursor.getInt(6));
                sdl.add(sd);
                cursor.moveToNext();
            }
        }
        return sdl;
    }
    /**
     *  @author zelonghu
     *  @time 2019/4/19  2:19 PM
     *  @describe drop a table
     */
    public static void droptable(SQLiteDatabase db,String tablename){
        db.execSQL("drop table " + tablename );
    }

    /**
     * @author zelonghu
     * @time 2019/4/13  9:18 PM
     * @describe 以时间顺序逆序读取所有的数据
     *              get all the SensorData into a List from the database desc
     */
    public static List<SensorData> querySDDesc(SQLiteDatabase db, String tablename) {
        List<SensorData> sdl = new ArrayList<SensorData>();
        try {
            //查询获得游标
            Cursor cursor = db.query(tablename, null, null,
                    null, null, null, null);
            //判断游标是否为空
            if (cursor.moveToLast()) {
                //遍历游标
                Log.i("cursor count", cursor.getCount() + "");
                while (!cursor.isBeforeFirst()) {
                    SensorData sd = new SensorData();
                    int id = cursor.getInt(0);
                    sd.setTime(cursor.getString(1));
                    sd.setType(cursor.getInt(2));
                    sd.setValue1(cursor.getDouble(3));
                    sd.setValue2(cursor.getDouble(4));
                    sd.setValue3(cursor.getDouble(5));
                    sd.setFlag(cursor.getInt(6));
                    sdl.add(sd);
                    cursor.moveToPrevious();
                }
            }
        }catch (Exception e){
            //第一次开启会出现没有建表的情况，报错以后创建新表
            //errors happens when app inited ,that is because table not exit ,here create the table
            e.printStackTrace();
            createTableSDL(db);
        }
        return sdl;
    }
}
